IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_PageReallocate]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_PageReallocate]
GO
create procedure [dbo].[cms_sp_PageReallocate]
(
@PageId [int],
@ReallocateToPageId [int]
)
as
declare @CurrentPageNodeId hierarchyid
declare @NewPageNodeId hierarchyid
declare @OldParentPageNodeId hierarchyid
declare @NewParentPageNodeId hierarchyid
declare @SiteId int
declare @SiteGroupId int
select 
	@CurrentPageNodeId = PageNodeId,
	@OldParentPageNodeId = PageNodeId.GetAncestor(1),
	@SiteId = SiteId,
	@SiteGroupId = SiteGroupId 
from dbo.Pages
where PageId = @PageId

select 
	@NewParentPageNodeId = PageNodeId
from dbo.Pages
where PageId = @ReallocateToPageId

set @NewPageNodeId = @NewParentPageNodeId.GetDescendant(
	(select 
		max(PageNodeId) 
	from dbo.Pages 
	where PageNodeId.GetAncestor(1) = @NewParentPageNodeId and
	((Pages.SiteGroupId is null and Pages.SiteId is null) or 
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId is null) or
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId = @SiteId))), null)
	
update dbo.Pages
	set PageNodeId = PageNodeId.GetReparentedValue(@CurrentPageNodeId, @NewPageNodeId)
where PageNodeId.IsDescendantOf(@CurrentPageNodeId) = 1 and
	((Pages.SiteGroupId is null and Pages.SiteId is null) or 
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId is null) or
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId = @SiteId))

GO

